package bdd;

import beans.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Vector;

/**
 * Appel des queries pour : - Gérer les comptes client (Création, Modification,
 * Désactivation, Gestion du mot de passe) - Afficher les comptes client -
 * Rapport d'états sur la clientèle (activité, démographie, newsletter)
 *
 * @author Loic Jacolin-Lescot
 */
public class QueriesClients {

    public Connection con;

    public QueriesClients() throws ClassNotFoundException, SQLException {
        con = ConnectBDD.getSingleton();
    }

    public ArrayList<CliClients> listerClients() throws SQLException {
        ArrayList<CliClients> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerClients}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            CliClients cli = new CliClients();
            cli.setCliId(res.getInt("CLI_ID"));
            cli.setCliNom(res.getString("CLI_NOM"));
            cli.setCliPrenom(res.getString("CLI_PRENOM"));
            cli.setCliLogin(res.getString("CLI_LOGIN"));
            cli.setCliMdp(res.getString("CLI_MDP"));
            cli.setCliDateNaissance(res.getDate("CLI_DATENAISSANCE"));
            cli.setCliSexe(res.getString("CLI_SEXE"));
            cli.setCliNewsletter(res.getBoolean("CLI_NEWSLETTER"));
            cli.setCliActif(res.getBoolean("CLI_ACTIF"));
            liste.add(cli);
        }

        ConnectBDD.fermer();
        return liste;
    }

    public CliClients listerUnClient(int id) throws SQLException {

        CallableStatement cstmt = con.prepareCall("{call ps_listerUnClient(?)}");
        cstmt.setInt(1, id);
        ResultSet res = cstmt.executeQuery();

        CliClients cli = new CliClients();
        while (res.next()) {
            cli.setCliId(res.getInt("CLI_ID"));
            cli.setCliNom(res.getString("CLI_NOM"));
            cli.setCliPrenom(res.getString("CLI_PRENOM"));
            cli.setCliLogin(res.getString("CLI_LOGIN"));
            cli.setCliMdp(res.getString("CLI_MDP"));
            cli.setCliDateNaissance(res.getDate("CLI_DATENAISSANCE"));
            cli.setCliSexe(res.getString("CLI_SEXE"));
            cli.setCliNewsletter(res.getBoolean("CLI_NEWSLETTER"));
            cli.setCliActif(res.getBoolean("CLI_ACTIF"));
        }

        ConnectBDD.fermer();
        return cli;
    }

    public ArrayList listerFichierClient() throws SQLException {
        ArrayList liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listingClientAffichage}");
        ResultSet res = cstmt.executeQuery();


        while (res.next()) {
            Vector tmp = new Vector();
            String s = res.getString("CLI_DATENAISSANCE");
            int age = (((int) ((System.currentTimeMillis()) / 1000 / 3600 / 24 / 365.25 + 1970)) - (Integer.parseInt("" + s.subSequence(0, 4))));
            tmp.add(res.getString("CLI_ID"));
            tmp.add(res.getString("CLI_NOM"));
            tmp.add(res.getString("CLI_PRENOM"));
            tmp.add(res.getString("CLI_LOGIN"));
            tmp.add(res.getString("CLI_SEXE"));
            tmp.add(s.substring(0, 10));
            tmp.add(age);
            tmp.add(res.getString("CLI_NEWSLETTER"));
            tmp.add(res.getString("CLI_ACTIF"));
            tmp.add(res.getString("ADR_RUE1"));
            tmp.add(res.getString("ADR_RUE2"));
            tmp.add(res.getString("ADR_CP"));
            tmp.add(res.getString("ADR_VILLE"));
            tmp.add(res.getString("ADR_SOCIETE"));
            tmp.add(res.getString("ADR_PAYS"));
            tmp.add(res.getString("PHO_NUM"));
            liste.add(tmp);

        }

        ConnectBDD.fermer();
        return liste;
    }

    public int desactClient(CliClients cli) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_desactClients(?,?,?)}");

        cstmt.setBoolean(1, cli.isCliActif());
        cstmt.setInt(2, cli.getCliId());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int modifierCliMdp(CliClients cli) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_modifierCliMdp(?,?,?)}");

        cstmt.setInt(2, cli.getCliId());
        cstmt.setString(1, cli.getCliMdp());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;

    }

    public int modifierClient(CliClients cli) throws SQLException {
        SimpleDateFormat dateMask = new SimpleDateFormat("yyyy-MM-dd");

        CallableStatement cstmt = con.prepareCall("{call ps_modifierClient(?,?,?,?,?,?,?,?,?,?)}");

        cstmt.setInt(1, cli.getCliId());
        cstmt.setString(2, cli.getCliNom());
        cstmt.setString(3, cli.getCliPrenom());
        cstmt.setString(4, cli.getCliLogin());
        cstmt.setString(5, cli.getCliMdp());
        cstmt.setString(6, cli.getCliSexe());
        cstmt.setString(7, dateMask.format(cli.getCliDateNaissance()));
        cstmt.setBoolean(8, cli.isCliActif());
        cstmt.setBoolean(9, cli.getCliNewsletter());
        cstmt.registerOutParameter(10, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(10);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int creerClient(CliClients cli) throws SQLException {
        SimpleDateFormat dateMask = new SimpleDateFormat("yyyy-MM-dd");

        CallableStatement cstmt = con.prepareCall("{call ps_creerClient(?,?,?,?,?,?,?,?,?)}");

        cstmt.setString(1, cli.getCliNom());
        cstmt.setString(2, cli.getCliPrenom());
        cstmt.setString(3, cli.getCliLogin());
        cstmt.setString(4, cli.getCliMdp());
        cstmt.setString(5, dateMask.format(cli.getCliDateNaissance()));
        cstmt.setBoolean(6, cli.getCliNewsletter());
        cstmt.setString(7, cli.getCliSexe());
        cstmt.setBoolean(8, cli.isCliActif());
        cstmt.registerOutParameter(9, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(9);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public ArrayList<CliAdr> listerAdr(int id) throws SQLException {
        ArrayList<CliAdr> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerCliAdr(?)}");
        cstmt.setInt(1, id);
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            CliAdr adr = new CliAdr();
            adr.setFkTadLabel(res.getString("TAD_LABEL"));
            adr.setAdrRue1(res.getString("ADR_RUE1"));
            adr.setAdrRue2(res.getString("ADR_RUE2"));
            adr.setAdrVille(res.getString("ADR_VILLE"));
            adr.setAdrCp(res.getString("ADR_CP"));
            adr.setAdrPays(res.getString("ADR_PAYS"));
            adr.setAdrSociete(res.getString("ADR_SOCIETE"));
            adr.setAdrId(res.getInt("ADR_ID"));
            adr.setAdrActif(res.getBoolean("ADR_ACTIF"));

            liste.add(adr);
        }

        ConnectBDD.fermer();
        return liste;
    }

    public ArrayList<CliPhones> listerPhones(int id) throws SQLException {
        ArrayList<CliPhones> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerCliPhones(?)}");
        cstmt.setInt(1, id);
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            CliPhones phone = new CliPhones();
            phone.setFkTtpLabel(res.getString("TTP_LABEL"));
            phone.setPhoNum(res.getString("PHO_NUM"));
            phone.setPhoId(res.getInt("PHO_ID"));
            phone.setPhoActif(res.getBoolean("PHO_ACTIF"));
            liste.add(phone);
        }

        ConnectBDD.fermer();
        return liste;
    }

    public ArrayList<CliAdr> listerAdrActif(int id) throws SQLException {
        ArrayList<CliAdr> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerCliAdrActif(?)}");
        cstmt.setInt(1, id);
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            CliAdr adr = new CliAdr();
            adr.setFkTadLabel(res.getString("TAD_LABEL"));
            adr.setAdrRue1(res.getString("ADR_RUE1"));
            adr.setAdrRue2(res.getString("ADR_RUE2"));
            adr.setAdrVille(res.getString("ADR_VILLE"));
            adr.setAdrCp(res.getString("ADR_CP"));
            adr.setAdrPays(res.getString("ADR_PAYS"));
            adr.setAdrSociete(res.getString("ADR_SOCIETE"));
            adr.setAdrId(res.getInt("ADR_ID"));
            adr.setAdrActif(res.getBoolean("ADR_ACTIF"));

            liste.add(adr);
        }

        ConnectBDD.fermer();
        return liste;
    }

    public ArrayList<CliPhones> listerPhonesActif(int id) throws SQLException {
        ArrayList<CliPhones> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerCliPhonesActif(?)}");
        cstmt.setInt(1, id);
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            CliPhones phone = new CliPhones();
            phone.setFkTtpLabel(res.getString("TTP_LABEL"));
            phone.setPhoNum(res.getString("PHO_NUM"));
            phone.setPhoId(res.getInt("PHO_ID"));
            phone.setPhoActif(res.getBoolean("PHO_ACTIF"));
            liste.add(phone);
        }

        ConnectBDD.fermer();
        return liste;
    }

    
    public int ajouterPhone(CliPhones pho) throws SQLException {

        CallableStatement cstmt = con.prepareCall("{call ps_ajouterNumeroPhone(?,?,?,?,?)}");

        cstmt.setString(1, pho.getPhoNum());
        cstmt.setBoolean(2, pho.isPhoActif());
        cstmt.setInt(3, pho.getFkCliId());
        cstmt.setInt(4, pho.getFkTtpId());
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(5);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int ajouterAdr(CliAdr adr) throws SQLException {

        CallableStatement cstmt = con.prepareCall("{call ps_ajouterCliAdresse(?,?,?,?,?,?,?,?,?,?)}");

        cstmt.setString(1, adr.getAdrRue1());
        cstmt.setString(2, adr.getAdrRue2());
        cstmt.setString(3, adr.getAdrVille());
        cstmt.setString(4, adr.getAdrCp());
        cstmt.setString(5, adr.getAdrPays());
        cstmt.setString(6, adr.getAdrSociete());
        cstmt.setInt(7, adr.getFkCliId());
        cstmt.setInt(8, adr.getFkTadId());
        cstmt.setBoolean(9, adr.isAdrActif());
        cstmt.registerOutParameter(10, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(10);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int desactCliAdr(CliAdr adr) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_desactCliAdr(?,?,?)}");

        cstmt.setBoolean(1, adr.isAdrActif());
        cstmt.setInt(2, adr.getAdrId());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int desactCliPhone(CliPhones pho) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_desactCliPhone(?,?,?)}");

        cstmt.setBoolean(1, pho.isPhoActif());
        cstmt.setInt(2, pho.getPhoId());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;
    }
}
